package com.xm_jd.model.dao;

import java.util.List;

import com.xm_jd.model.entitys.GoodsDetailVO;
import com.xm_jd.model.entitys.pingpai;
import com.xm_jd.model.entitys.shoop;
import com.xm_jd.utils.BaseDao;

public class NewDao {
	BaseDao<shoop> sdao= new BaseDao<>(shoop.class);	
	BaseDao<GoodsDetailVO> spdao= new BaseDao<>(GoodsDetailVO.class);
	BaseDao<pingpai> pdao = new BaseDao<>(pingpai.class);
	
	public List<pingpai> getselectbrand(int id){
		String sql = "SELECT * FROM tb_shoop  INNER JOIN tb_pingpai on tb_shoop.shoop_Brand=tb_pingpai.pingpai_id WHERE shoop_Id="+id+";";
		return pdao.selectAll(sql, null);
	}
	/**
	 * @param
	 * @return
	 * 根据编号查询商品的简短描述,价格
	 */
	public List<shoop> getselectAll(int id){
		String sql = "SELECT * FROM tb_shoop where shoop_Id="+id+";";
		return sdao.selectAll(sql, null);
	}
	
	/**
	 * 根据品牌编号和对应的属性名编号，查属性值
	 * @return
	 */
	public List<GoodsDetailVO> getAllXssx(int  brand,int xssx_id){
		String sql = "select DISTINCT xssxz_zhi from tb_shoop inner join tb_xssxz on tb_shoop.shoop_Id=tb_xssxz.xssxz__shoop_id "+ 
						" inner join tb_xssx on tb_xssxz.xssxz_xssx_id=tb_xssx.xssx_id WHERE tb_shoop.shoop_Brand=? and xssx_id=?";
		return spdao.selectAll(sql, new Object[]{brand,xssx_id});
	}
	/**
	 * @param id
	 * @return
	 *  根据商品编号查询所有的商品属性
	 */
	public List<GoodsDetailVO> getseletspAll(int id){
		String sql ="SELECT * FROM tb_spsx INNER JOIN tb_sxz on tb_sxz.sxz_spsxz_id=tb_spsx.spsx_id WHERE sxz_shoop_id="+id+";";
		return spdao.selectAll(sql, null);
		
	}
	
	/**
	 * @return
	 * 查询销售的属性 （选择颜色，选择容量）
	 */
	public List<GoodsDetailVO> getseletxssxAll(){
		String sql = "SELECT * FROM tb_xssx ";
		return spdao.selectAll(sql, null);
	}
	
	/**
	 * @param id
	 * @return
	 * 查询某商品所对应的图片
	 */
	public List<GoodsDetailVO> getselectUrl(int id){
		String sql ="SELECT * FROM tb_picture INNER JOIN tb_shoop ON tb_shoop.shoop_Id= tb_picture.picture_shoop_Id WHERE shoop_Id="+id+";";
		return spdao.selectAll(sql, null);
	}
	
	/**
	 * @param id
	 * @return
	 * 查询某商品的颜色
	 */
	public List<GoodsDetailVO> getseletcolorsp(int id){
		String sql ="SELECT * FROM tb_xssxz INNER JOIN tb_xssx on tb_xssx.xssx_id=tb_xssxz.xssxz_xssx_id WHERE xssxz__shoop_id="+id+" and xssx_id=1;";
		return spdao.selectAll(sql, null);
	}
	
	/**
	 * @param id
	 * @return
	 * 查询某商品的内存
	 */
	public List<GoodsDetailVO> getseletROMsp(int id){
		String sql ="SELECT * FROM tb_xssxz INNER JOIN tb_xssx on tb_xssx.xssx_id=tb_xssxz.xssxz_xssx_id WHERE xssxz__shoop_id="+id+" and xssx_id=2;";
		return spdao.selectAll(sql, null);
	}
	
	/**
	 * @param id
	 * @return
	 * 查询某商品的所有的属性值
	 */
	public List<GoodsDetailVO> getsletspsxAll(int id){
		String sql ="SELECT *FROM tb_spsx INNER JOIN tb_sxz on tb_sxz.sxz_spsxz_id=tb_spsx.spsx_id WHERE sxz_shoop_id="+id+";";
		return spdao.selectAll(sql, null);
	}
	
	public List<GoodsDetailVO> getShoopId(String color,String rom){
		String sql = "select * from tb_shoop inner join tb_xssxz on xssxz__shoop_id=shoop_Id where xssxz_zhi=? and shoop_Id in("+
				"select shoop_Id from tb_shoop inner join tb_xssxz on xssxz__shoop_id=shoop_Id where xssxz_zhi=?)";	
		return spdao.selectAll(sql, new Object[]{color,rom});
	}
	
}
